﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Collections.ObjectModel;
/// <summary>
///AccessHelper 的摘要说明
/// </summary>
/// 
namespace CommunitySystem.BO
{
    public class AccessHelper
    {
        private string strConnection { get; set; }
        private static AccessHelper instance = null;
        //private static SqlConnection connection = null;
        public static AccessHelper GetInstance()
        {
            if (instance == null)
                instance = new AccessHelper();
            return instance;
        }


        public AccessHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
            strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["JinYang"].ConnectionString;
        }
        public string GetConnectionStr()
        {
            return strConnection;
        }
        //private  string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ZhouPuCommunityDB"].ConnectionString;
        public DataTable GetDataSet(string sql)
        {
            SqlConnection objConnection = new SqlConnection(strConnection);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, objConnection);
                sda.Fill(dt);
                objConnection.Open(); return dt;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql)
        {
            SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, objConnection);
                sda.Fill(ds);
                objConnection.Open(); return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql, SqlParameter[] paras)
        {

            SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, objConnection);
                if (paras != null)
                {
                    sda.SelectCommand.Parameters.AddRange(paras);
                }
                sda.Fill(ds);
                objConnection.Open(); return ds;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql, SqlParameter[] paras, SqlConnection conn)
        {

            //SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                if (paras != null)
                {
                    sda.SelectCommand.Parameters.AddRange(paras);
                }
                sda.Fill(ds);
                return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {

            }
        }


        public DataSet ExcuteDataSet(string spname, string svalue)
        {

            SqlConnection objConnection = new SqlConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                SqlCommand comm = new SqlCommand(spname, objConnection);
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.VarChar));
                comm.Parameters["@sqlWhere"].Value = svalue;
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = comm;
                sda.Fill(ds);
                objConnection.Open(); return ds;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public bool ExcultData(string sql)
        {
            SqlConnection objConnection = new SqlConnection(strConnection);
            SqlCommand objcmd = new SqlCommand(sql, objConnection);
            try
            {
                if (objConnection.State == ConnectionState.Closed) { objConnection.Open(); }
                return objcmd.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (OleDbException ob)
            {
                throw ob;
            }
            finally { objConnection.Close(); }

        }
        public int ExecuteSql(String Sqlstr, SqlParameter[] param)
        {
            using (SqlConnection objConnection = new SqlConnection(strConnection))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = objConnection;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                objConnection.Open();
                cmd.ExecuteNonQuery();
                objConnection.Close();
                return 1;
            }
        }
        public int ExecuteNonQuery(string sql)
        {
            SqlConnection _Connection = new SqlConnection(strConnection);
            if (_Connection.State == ConnectionState.Closed)
                _Connection.Open();
            System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand(sql, _Connection);
            return com.ExecuteNonQuery();
        }


        /// <summary>
        /// 开始事务
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public SqlTransaction BeginTransaction(out SqlConnection conn)
        {
            conn = new SqlConnection(strConnection);
            conn.Open();
            return conn.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="transction"></param>
        public void EndTransaction(SqlTransaction transction)
        {
            transction.Commit();
            if (transction.Connection != null)
            {
                transction.Connection.Close();
                transction.Connection.Dispose();
            }
        }
        public void EndTransaction(SqlTransaction transction, SqlConnection conn)
        {
            transction.Commit();
            conn.Close();
            conn.Dispose();
        }


        public object ExecuteScalar(String Sqlstr, SqlParameter[] param = null)
        {
            using (SqlConnection objConnection = new SqlConnection(strConnection))
            {

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = objConnection;
                cmd.CommandText = Sqlstr;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                object res = cmd.ExecuteScalar();
                objConnection.Close();
                return res;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="DonotUpdateColumn"></param>
        /// <param name="key">key如果为空，主键为ID</param>
        /// <param name="tableName">如果tableName为空，表名为实体名</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string GeneratorInsertSQL(object entity, string[] DonotInsertColumn, string key, string tableName, out List<SqlParameter> parameters)
        {
            parameters = new List<SqlParameter>();
            if (entity == null)
                return "";            
            if (string.IsNullOrEmpty(key))
                key = "id";
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = entity.GetType().Name;
            }
            string sql = "insert into " + tableName + "("; 
            string value = " \n\t values (";
            foreach (var p in entity.GetType().GetProperties())
            {
                string PIName = p.Name;
                if (p.Name.ToLower() == key.ToLower())
                    continue;
                if (DonotInsertColumn != null && DonotInsertColumn.Contains(p.Name))
                    continue;
                sql += "["+p.Name + "], ";
                value += "@" + p.Name + ",";
                object pvalue = p.GetValue(entity, null);
                if(pvalue == null)
                    pvalue = System.DBNull.Value;
                parameters.Add(new SqlParameter("@" + p.Name, pvalue));
            }
            sql = sql.TrimEnd().TrimEnd(',') +")";
            value = value.TrimEnd().TrimEnd(',') + ")";
            sql = sql + value + "    \n\t SELECT @@IDENTITY;";
            return sql;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="DonotUpdateColumn"></param>
        /// <param name="key">key如果为空，主键为ID</param>
        /// <param name="tableName">如果tableName为空，表名为实体名</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string GeneratorUpdateSQL(object entity, string[] DonotUpdateColumn, string key, string tableName, out List<SqlParameter> parameters)
        {
            parameters = new List<SqlParameter>();
            if (entity == null)
                return "";
            if (string.IsNullOrEmpty(key))
                key = "id";
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = entity.GetType().Name;
            }
            string sql = "update  " + tableName + "   set   ";
            //string value = " \n\t values (";
            foreach (var p in entity.GetType().GetProperties())
            {
                string PIName = p.Name;
                if (p.Name.ToLower() == key.ToLower())
                {
                    key = p.Name;
                    object idvalue = p.GetValue(entity, null);
                    if (idvalue == null)
                        idvalue = System.DBNull.Value;
                    parameters.Add(new SqlParameter("@" + p.Name, idvalue));
                    continue;
                }

                

                if (DonotUpdateColumn != null && DonotUpdateColumn.Contains(p.Name))
                    continue;
                sql += "[" + p.Name + "]=@" + p.Name + ", ";
                //value += "@" + p.Name + ",";
                object pvalue = p.GetValue(entity, null);
                if (pvalue == null)
                    pvalue = System.DBNull.Value;
                parameters.Add(new SqlParameter("@" + p.Name, pvalue));
               
            }
            sql = sql.TrimEnd().TrimEnd(',') + "   where [" + key + "]=@" + key;
            return sql;
        }

        public static SqlParameter[] GetParameter(object entity, string[] WithoutColumn)
        {
            if(entity == null)
                return null;
            List<SqlParameter> list = new List<SqlParameter>();
            foreach (var p in entity.GetType().GetProperties())
            {
                if (WithoutColumn != null && WithoutColumn.Contains(p.Name))
                    continue;
                object value = p.GetValue(entity, null);
                if (value == null)
                    value = System.DBNull.Value;
                list.Add(new SqlParameter("@"+p.Name, value));
            }
            return list.ToArray();
        }

        /// <summary>
        /// 将一个DataRow 转换成实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static T FillDataRowToEntity<T>(DataTable table, DataRow dr) where T : new()
        {
            T entity = new T();
            foreach (PropertyInfo pi in entity.GetType().GetProperties())
            {
                if (table.Columns.Contains(pi.Name) && dr[pi.Name] != null && dr[pi.Name] != System.DBNull.Value)
                {
                    pi.SetValue(entity, dr[pi.Name], null);
                }
            }
            return entity;
        }

        /// <summary>
        /// 通用插入方法，目前只支持主键为ID的实体插入更新操作, 且不支持有子表操作
        /// </summary>
        /// <param name="entity">插入实体</param>       
        /// <param name="table">表名,如果为空，则默认为实体名</param>
        /// <param name="IgnoreColumne">忽略字段</param>
        public  int SaveEntity(object entity, string table, List<string> IgnoreColumne)
        {
            string key = "ID";
            try
            {
                PropertyInfo pi = entity.GetType().GetProperty(key);
                int _id = (int)pi.GetValue(entity, null);
                if (_id > 0)
                {
                    UpdateEnity(entity, table, IgnoreColumne);
                    return _id;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("主键必须为int类型的ID");
            }
            List<SqlParameter> list = null;
            string sql = null;
            if(IgnoreColumne != null)
                sql = AccessHelper.GeneratorInsertSQL(entity, IgnoreColumne.ToArray(), key, table, out list);
            else
                sql = AccessHelper.GeneratorInsertSQL(entity, null, key, table, out list);
            DataSet ds =  AccessHelper.GetInstance().ExcuteDataSet(sql, list.ToArray());
            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }

        /// <summary>
        /// 同Save
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="table"></param>
        /// <param name="IgnoreColumne"></param>
        private  void UpdateEnity(object entity, string table, List<string> IgnoreColumne)
        {
            string key = "ID";
            List<SqlParameter> list = null;
            string sql = null;
            if (IgnoreColumne != null)
                sql = AccessHelper.GeneratorUpdateSQL(entity, IgnoreColumne.ToArray(), key, table, out list);
            else
                sql = AccessHelper.GeneratorUpdateSQL(entity, null, key, table, out list);
            //string sql = AccessHelper.GeneratorUpdateSQL(entity, IgnoreColumne.ToArray(), key, table, out list);
            AccessHelper.GetInstance().ExecuteSql(sql, list.ToArray());
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public ObservableCollection<T> GetEnity<T>(string condition, string table) where T : new()
        {
            ObservableCollection<T> collection = new ObservableCollection<T>();
            T t = new T();
            StringBuilder sql = new StringBuilder("select * from  ");
            if (string.IsNullOrEmpty(table))
                table = t.GetType().Name;
            sql.Append(table);
            if (!string.IsNullOrEmpty(condition))
            {
                sql.Append(" where ");
                sql.Append(condition);
            }
            DataSet ds = this.ExcuteDataSet(sql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    collection.Add(AccessHelper.FillDataRowToEntity<T>(ds.Tables[0], dr));
                }
            }
            return collection;
        }

        public bool DeleteEntity(string tableName, int id)
        {
            StringBuilder sb = new StringBuilder("delete ");
            sb.Append(tableName);
            sb.Append(" where id=");
            sb.Append(id);
            int count = ExecuteNonQuery(sb.ToString());
            if (count == 1)
                return true;
            else
                return false;

        }
    }
}
